package com.css.fxfzypg.modules.baseData.repository.impl;

import com.alibaba.fastjson.JSONObject;
import com.css.fxfzypg.constants.FxfzConstants;
import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.baseData.entity.CountiesCondition;
import com.css.fxfzypg.modules.baseData.queryParams.CountiesConditionQueryParams;
import com.css.fxfzypg.modules.baseData.repository.CountiesConditionRepository;
import com.css.fxfzypg.util.CreateGson;
import com.css.fxfzypg.util.PlatformDateUtils;
import com.css.fxfzypg.util.PlatformObjectUtils;
import com.css.fxfzypg.util.PlatformSessionUtils;
import com.css.fxfzypg.web.PlatformSessionContext;
import com.css.fxfzypg.zcpt.sys.entity.SUser;
import com.css.fxfzypg.zcpt.sys.service.SUserService;
import com.css.fxfzypg.zcpt.sys.service.SysRoleService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.sql.Timestamp;
import java.util.List;
import java.util.Map;

@Repository
public class CountiesConditionRepositoryImpl implements CountiesConditionRepository {
    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Resource
    SysRoleService sysRoleService;

    @Resource
    SUserService sUserService;

    @Override
    @Transactional(rollbackFor = Exception.class)
    public int updateCountiesCondition(CountiesCondition countiesCondition) {
        String sql = "update sys_countiescondition set cc_landform = :ccLandform, " +
                "cc_seismotectonics = :ccSeismotectonics, cc_climate = :ccClimate, cc_nation = :ccNation, " +
                "cc_economy = :ccEconomy, cc_traffic = :ccTraffic, cc_disaster = :ccDisaster, " +
                "cc_facility = :ccFacility, cc_history = :ccHistory, cc_trait = :ccTrait, cc_school = :ccSchool, " +
                "cc_hospital = :ccHospital, cc_hazard = :ccHazard, cc_reservoir = :ccReservoir, cc_message = " +
                ":ccMessage,cc_landform_attachment = :ccLandformAttachment, " +
                "cc_seismotectonics_attachment = :ccSeismotectonicsAttachment, " +
                "cc_climate_attachment = :ccClimateAttachment, cc_nation_attachment = :ccNationAttachment, " +
                "cc_economy_attachment = :ccEconomyAttachment, cc_traffic_attachment = :ccTrafficAttachment, " +
                "cc_disaster_attachment = :ccDisasterAttachment, cc_facility_attachment = :ccFacilityAttachment, " +
                "cc_history_attachment = :ccHistoryAttachment, cc_trait_attachment = :ccTraitAttachment, " +
                "cc_school_attachment = :ccSchoolAttachment, cc_hospital_attachment = :ccHospitalAttachment, " +
                "cc_hazard_attachment = :ccHazardAttachment, cc_reservoir_attachment = :ccReservoirAttachment, " +
                "cc_message_attachment = :ccMessageAttachment, update_user = :updateUser, update_time = :updateTime " +
                "where cc_id = :ccId and is_delete = '" + YNEnum.N.toString() + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(countiesCondition);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return i;
    }

    @Override
    @Transactional
    public int saveCountiesCondition(CountiesCondition countiesCondition) {
        String sql = "insert into sys_countiescondition(cc_id,province,city,prefecture,cc_landform," +
                "cc_seismotectonics,cc_climate,cc_nation,cc_economy,cc_traffic,cc_disaster,cc_facility,cc_history," +
                "cc_trait,cc_school,cc_hospital,cc_hazard,cc_reservoir,cc_message,cc_landform_attachment," +
                "cc_seismotectonics_attachment,cc_climate_attachment,cc_nation_attachment,cc_economy_attachment," +
                "cc_traffic_attachment,cc_disaster_attachment,cc_facility_attachment,cc_history_attachment," +
                "cc_trait_attachment,cc_school_attachment,cc_hospital_attachment,cc_hazard_attachment," +
                "cc_reservoir_attachment,cc_message_attachment,is_delete,create_user,create_time,province_id,city_id," +
                "prefecture_id,province_code, city_code, prefecture_code) " +
                "values(:ccId,:province,:city,:prefecture,:ccLandform,:ccSeismotectonics,:ccClimate,:ccNation," +
                ":ccEconomy,:ccTraffic,:ccDisaster,:ccFacility,:ccHistory,:ccTrait,:ccSchool,:ccHospital,:ccHazard," +
                ":ccReservoir,:ccMessage,:ccLandformAttachment,:ccSeismotectonicsAttachment,:ccClimateAttachment," +
                ":ccNationAttachment,:ccEconomyAttachment,:ccTrafficAttachment,:ccDisasterAttachment," +
                ":ccFacilityAttachment,:ccHistoryAttachment,:ccTraitAttachment,:ccSchoolAttachment," +
                ":ccHospitalAttachment,:ccHazardAttachment,:ccReservoirAttachment,:ccMessageAttachment," +
                ":isDelete,:createUser,:createTime,:provinceId,:cityId,:prefectureId,:provinceCode, :cityCode, :prefectureCode)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(countiesCondition);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        return namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public JSONObject getAllCountiesConditionParams(CountiesConditionQueryParams queryParams, int curPage, int pageSize) {
        StringBuilder str = new StringBuilder("");
        StringBuilder sql = new StringBuilder(" select  c.* from sys_countiescondition c ");
        StringBuilder whereSql = new StringBuilder(" where c.is_delete = '" + YNEnum.N.toString() + "'");
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and c.province_id ='" + queryParams.getProvinceId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and c.city_id  ='" + queryParams.getCityId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getPrefectureId())) {
            whereSql.append(" and c.prefecture_id ='" + queryParams.getPrefectureId() + "'");
        }
        String userId = PlatformSessionContext.getUserID();
        String user = sUserService.getSUser(userId);
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        String role = sysRoleService.getRoleByUserId(userId);
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        for (Map roleMap : roleMaps) {
            str.append(roleMap.get("roleCode") + ",");
        }
        String roles = str.toString();
        if (roles.contains(FxfzConstants.YPG_COUN_GL_ROLE) || roles.contains(FxfzConstants.YPG_COUN_YW_ROLE)) {
            //国家级管理员和国家级业务人员可以全看全部
        } else if (roles.contains(FxfzConstants.YPG_PRI_GL_ROLE) || roles.contains(FxfzConstants.YPG_PRI_YW_ROLE)) {
            //省级管理员和省级业务人员可以全看自己
            whereSql.append(" and province_id = '" + sUser.getProvinceId() + "'");
        }
        StringBuilder orderSql = new StringBuilder();
        if ("0".equals(queryParams.getOption())) {
            orderSql.append(" order by c.province_code,c.city_code,c.prefecture_code");
        }
        if ("1".equals(queryParams.getOption())) {
            orderSql.append(" order by COALESCE (c.update_time,c.create_time) desc");
        }
//        StringBuilder orderSql = new StringBuilder(" order by c.prefecture_code");
        sql.append(whereSql).append(orderSql).toString();
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(CountiesCondition.class)).size();
        List<CountiesCondition> gdpEntities = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(CountiesCondition.class));
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("rows", gdpEntities);
        jsonObject.put("total", total);
        return jsonObject;
    }

    @Override
    public CountiesCondition getCountiesConditionById(String id) {
        String sql = "select * from sys_countiescondition where cc_id = '" + id + "' and is_delete = '" + YNEnum.N.toString() + "'";
        CountiesCondition countiesCondition = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(CountiesCondition.class));
        return countiesCondition;
    }

    @Override
    public CountiesCondition getCountiesConditionBydivision(String provinceId, String cityId, String prefectureId) {
        String sql = "select * from sys_countiescondition where province_id ='" + provinceId + "' and city_id ='" + cityId + "'" +
                " and prefecture_id ='" + prefectureId + "' and is_delete='" + YNEnum.N.toString() + "'";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(CountiesCondition.class));
        } catch (Exception e) {
            return null;
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void deleteCountiesConditionsById(String id, Timestamp timestamp, String userId) {
        String sql = "update sys_countiescondition set is_delete = '" + YNEnum.Y.toString() +
                "', update_time = '" + timestamp + "', update_user = '" + userId + "' where cc_id ='" + id + "' and is_delete = '" + YNEnum.N.toString() + "'";
        jdbcTemplate.update(sql);
    }

    @Override
    public List<CountiesCondition> getAll() {
        return jdbcTemplate.query("select * from sys_countiescondition where is_delete = '" + YNEnum.N.toString() + "'", new BeanPropertyRowMapper<>(CountiesCondition.class));
    }

    @Override
    public CountiesCondition getQxCountiesCodition(String province, String city, String area) {
        String sql = "select * from sys_countiescondition where province ='" + province + "' " +
                "  and is_delete='" + YNEnum.N.toString() + "'";
        if(StringUtils.isNotBlank(city)){
            sql+=" and city ='" + city + "' ";
        }
        if(StringUtils.isNotBlank(area)){
            sql+=" and prefecture ='" + area + "'";
        }
        List<CountiesCondition> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(CountiesCondition.class));
        return list.size() > 0 ? list.get(0) : null;

    }

    @Override
    public void delete(CountiesCondition condition) {
        String userId = PlatformSessionUtils.getUserId();
        Timestamp currentTimestamp = PlatformDateUtils.getCurrentTimestamp();
        String sql = "update sys_countiescondition set is_delete = '" + YNEnum.Y.toString() +
                "' ,update_time ='" + currentTimestamp + "', update_user='" + userId + "'" +
                " where province = '" + condition.getProvince() + "' and city = '" + condition.getCity() + "' and prefecture ='" + condition.getPrefecture() + "'";
        jdbcTemplate.update(sql);
    }

    @Override
    public Integer countByCounty(String province, String city, String county) {
        return jdbcTemplate.queryForObject("SELECT count(*) from sys_countiescondition where prefecture = '" + county + "' and city = '" + city + "' and province = '" + province + "' and is_delete ='0'", Integer.class);
    }

    @Override
    public Integer countByCity(String province, String city) {
        return jdbcTemplate.queryForObject("SELECT count(*) from sys_countiescondition where city = '" + city + "' and province = '" + province + "' and is_delete ='0' ", Integer.class);
    }

    @Override
    public Integer countByProvince(String name) {
        return jdbcTemplate.queryForObject("SELECT count(*) from sys_countiescondition where province = '" + name + "' and is_delete ='0'", Integer.class);
    }

    @Override
    public Integer countByProvinceAndYear(String province, String taskYear) {
        return jdbcTemplate.queryForObject("SELECT count(*) from sys_countiescondition where province = ? and (TO_CHAR(update_time, 'yyyy-MM-dd hh24:mi:ss') LIKE ? or TO_CHAR(create_time, 'yyyy-MM-dd hh24:mi:ss') LIKE ?) and is_delete ='0'", Integer.class, province, taskYear + "%", taskYear + "%");
    }
}
